package com.data.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.StateMin;

@Repository("stateMinDao")
public class StateMinDaoImpl extends TemplateDaoImpl<StateMin, Integer>implements StateMinDao{
	/**
	 * 根据主键查询设备状态半小时记录
	 * @param id
	 * @return
	 */
	@Override
	public StateMin getStateMinById(long id) {
		String sql = "select * from stateMin where id=:id";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", id);
		return jt.queryForObject(sql, params,
				ParameterizedBeanPropertyRowMapper.newInstance(StateMin.class));
	}
	/**
	 * 查询不同时间段内设备状态记录
	 * @param equId
	 * @param stateType
	 * @param shiftTime
	 * @param currentTime
	 * @return
	 */
	@Override
	public List<StateMin> getStateMins(int equId, String stateType,String shiftTime,String currentTime) {
		String sql="select * from stateMin where equId=:equId and  stateType=:stateType  and CONVERT(VARCHAR(16),belongTime,120)>=:shiftTime  and  CONVERT(VARCHAR(16),belongTime,120)<=:currentTime";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("stateType", stateType);
		params.put("shiftTime", shiftTime);
		params.put("currentTime", currentTime);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateMin.class));
	}
	/**
	 * 根据设备id和归属时间查询是否存已保存此记录
	 * @param equId
	 * @param belongDate
	 * @param stateType
	 * @return
	 */
	@Override
	public StateMin getStateMinByDate(int equId,String stateType, String belongTime) {
		String sql="select * from stateMin where equId=:equId  and stateType=:stateType  and CONVERT(VARCHAR(16),belongTime,120)=:belongTime";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("stateType", stateType);
		params.put("belongTime", belongTime);
		return jt.queryForObject(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateMin.class));
	}
	/**
	 * 根据主键删除
	 */
	@Override
	public void deleteByMinId(long minId) {
		String sql = "delete  from stateMin where id=:minId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", minId);
		jt.update(sql, params);
	}
	/**
	 * 获取当天的所有半小时该状态记录
	 * @param equId
	 * @param stateType
	 * @param belongTime(如:2016-06-16)
	 * @return
	 */
	@Override
	public List<StateMin> getStateMinsByDay(int equId, String stateType,
			String belongTime) {
		String sql="select * from stateMin where equId=:equId and  stateType=:stateType  and CONVERT(VARCHAR(10),belongTime,120)=:belongTime ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("stateType", stateType);
		params.put("belongTime", belongTime);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateMin.class));
	}
	/**
	 * 获取时间区间内的状态总时长
	 * @param equId
	 * @param stateType
	 * @param startDate(如：2016-07-29 08:00:00)
	 * @param endDate(如：2016-07-29 16:00:00)
	 * @return
	 */
	@Override
	public Double getDurationByTime(int equId, String stateType, String startDate, String endDate) {
		StringBuilder sql = new StringBuilder("select COALESCE(SUM(duration),0) from stateMin where equId=:equId   and CONVERT(VARCHAR(16),belongTime,120)>=:startDate  and  CONVERT(VARCHAR(16),belongTime,120)<:endDate");
		Map<String, Object> params=new HashMap<String, Object>();
		if(stateType==null||stateType.equals("")){
			sql.append(" and stateType<>'停机' ");
		}else{
			sql.append(" and stateType=:stateType ");
			params.put("stateType", stateType);
		}
		params.put("equId", equId);
		params.put("stateType", stateType);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.queryForObject(sql.toString(), params, Double.class);
	}
	/**
	 * 查询某状态的持续时间
	 * @param equId
	 * @param stateType
	 * @param belongDate
	 * @param isLow 是否最低值  是(Y) 否(N)
	 * @return
	 */
	@Override
	public List<StateMin> getDurationByStateType(int equId, String stateType, String belongDate,String isLow) {
		StringBuilder sql = new StringBuilder("select belongTime,duration from stateMin where equId=:equId  and stateType=:stateType  and CONVERT(VARCHAR(10),belongTime,120)=:belongDate and duration=");
		if(isLow.equals("Y")){
			sql.append(" (select MIN(duration) from stateMin ");
		}else{
			sql.append(" (select MAX(duration) from stateMin ");
		}
		sql.append(" where equId=:equId  and stateType=:stateType  and CONVERT(VARCHAR(10),belongTime,120)=:belongDate)");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("stateType", stateType);
		params.put("belongDate", belongDate);
		return jt.query(sql.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(StateMin.class));
	}
}
